/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Control;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.util.CellRangeAddressList;

/**
 *
 * @author Zeta
 */
public class GestorGeneradorExcel
{
    private int cantidadDeCriterios;
    private int cantidadDeAlternativas;
    private File archivoAGenerar;

    public GestorGeneradorExcel(int cc, int ca, File aG)
    {
        cantidadDeCriterios = cc;
        cantidadDeAlternativas = ca;
        archivoAGenerar = aG;
    }

    public boolean generarExcel(boolean criteriosXAlternativas)
    {
        try
        {
            File f;
            if (criteriosXAlternativas)
            {
                f = new File("." + File.separator + "res" + File.separator + "ModeloCriteriosXAlternativas.xls");
            }
            else
            {
                f = new File("." + File.separator + "res" + File.separator + "ModeloAlternativasXCriterios.xls");
            }
            HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(f));
            if (cantidadDeCriterios > 2 || cantidadDeAlternativas > 2)
            {
                HSSFSheet sheet = wb.getSheetAt(0);
                HSSFRow row;
                if (criteriosXAlternativas)
                {
                    for (int i = 2; i < cantidadDeAlternativas; i++)
                    {
                        addAlternativa(wb, sheet, 5, (i + 2));
                        addAlternativa(wb, sheet, 6, (i + 2));
                        addAlternativa(wb, sheet, 7, (i + 2));
                        row = sheet.getRow(5);
                        row.getCell((i + 2)).setCellValue("A" + (i + 1));
                    }
                    for (int i = 2; i < cantidadDeCriterios; i++)
                    {
                        copyRow(wb, sheet, 6, (i + 6));
                        row = sheet.getRow((i + 6));
                        row.getCell(1).setCellValue("C" + (i + 1));
                    }
                }
                else
                {
                    for (int i = 2; i < cantidadDeCriterios; i++)
                    {
                        addAlternativa(wb, sheet, 5, (i + 2));
                        addAlternativa(wb, sheet, 6, (i + 2));
                        addAlternativa(wb, sheet, 7, (i + 2));
                        row = sheet.getRow(5);
                        row.getCell((i + 2)).setCellValue("C" + (i + 1));
                    }
                    for (int i = 2; i < cantidadDeAlternativas; i++)
                    {
                        copyRow(wb, sheet, 6, (i + 6));
                        row = sheet.getRow((i + 6));
                        row.getCell(1).setCellValue("A" + (i + 1));
                    }
                }
                for (int i = 2; i < cantidadDeCriterios; i++)
                {
                    copyRow(wb, sheet, 3, (i + 2));
                    row = sheet.getRow((i + 2));
                    row.getCell(1).setCellValue("C" + (i + 1));
                }

                CellRangeAddressList addressList = new CellRangeAddressList(2, (cantidadDeCriterios + 1), 4, 4);
                DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint("ValoresFPR");
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                sheet.addValidationData(dataValidation);

                addressList = new CellRangeAddressList(2, (cantidadDeCriterios + 1), 3, 3);
                dvConstraint = DVConstraint.createFormulaListConstraint("ValoresTipo");
                dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                sheet.addValidationData(dataValidation);
            }
            FileOutputStream fileOut = new FileOutputStream(archivoAGenerar);
            wb.write(fileOut);
            fileOut.close();

        }
        catch (Exception e)
        {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    private static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum)
    {
        // Get the source / new row
        HSSFRow newRow = worksheet.getRow(destinationRowNum);
        HSSFRow sourceRow = worksheet.getRow(sourceRowNum);

        // If the row exist in destination, push down all rows by 1 else create a new row
        if (newRow != null)
        {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        }
        else
        {
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Loop through source columns to add to new row
        for (int i = 0; i < sourceRow.getLastCellNum(); i++)
        {
            // Grab a copy of the old/new cell
            HSSFCell oldCell = sourceRow.getCell(i);
            HSSFCell newCell = newRow.createCell(i);

            // If the old cell is null jump to next cell
            if (oldCell == null)
            {
                newCell = null;
                continue;
            }

            // Copy style from old cell and apply to new cell
            HSSFCellStyle newCellStyle = workbook.createCellStyle();
            newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
            newCell.setCellStyle(newCellStyle);

            // If there is a cell comment, copy
            if (newCell.getCellComment() != null)
            {
                newCell.setCellComment(oldCell.getCellComment());
            }

            // If there is a cell hyperlink, copy
            if (oldCell.getHyperlink() != null)
            {
                newCell.setHyperlink(oldCell.getHyperlink());
            }

            // Set the cell data type
            newCell.setCellType(oldCell.getCellType());

            // Set the cell data value
            switch (oldCell.getCellType())
            {
                case Cell.CELL_TYPE_BLANK:
                    newCell.setCellValue(oldCell.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    newCell.setCellValue(oldCell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_ERROR:
                    newCell.setCellErrorValue(oldCell.getErrorCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    newCell.setCellFormula(oldCell.getCellFormula());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    newCell.setCellValue(oldCell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    newCell.setCellValue(oldCell.getRichStringCellValue());
                    break;
            }
        }
    }

    private static void addAlternativa(HSSFWorkbook workbook, HSSFSheet worksheet, int numberOfRow, int numberOfCell)
    {
        HSSFRow row = worksheet.getRow(numberOfRow);
        HSSFCell copyCell = row.getCell(3);
        HSSFCell newCell = row.createCell(numberOfCell);

        HSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(copyCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        newCell.setCellType(copyCell.getCellType());

        // Set the cell data value
        switch (copyCell.getCellType())
        {
            case Cell.CELL_TYPE_BLANK:
                newCell.setCellValue(copyCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(copyCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(copyCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(copyCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(copyCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(copyCell.getRichStringCellValue());
                break;
        }
    }
}
